﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using HMS.BL;
using HMS.Common;
using System.Data.SqlClient;
using System.Web.Script.Serialization;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Collections.Specialized;
using HMS.Security;
using HMS.Logging;
using System.Xml;
using System.Xml.Serialization;
using System.IO;


namespace HMS.DL
{
    public class ConfigHierData
    {
        public string ClsName = "$ConfigHierData$";
        public string TableName { get; set; }
        public bool IsRoot { get; set; }
        public int ParentId { get; set; }

        public int ListId { get; set; }
        public string ListName { get; set; }

        public int ListColId { get; set; }
        public int id { get; set; }
        public int MasterId { get; set; }
        //  public coll reqformVal{ get; set; }
        public NameValueCollection allkeys { get; set; }
        public string srcVal { get; set; }
        public string sidx { get; set; }
        public string sord { get; set; }
        public int rows { get; set; }
        public int page { get; set; }
        public int limit { get; set; }
        public string hidColList { get; set; }
        //public int UserTypeId { get; set; }
        public Int32 HierarchyId { get; set; }
        public Int32 attr_id { get; set; }
        public int UserId { get; set; }
        public string query { get; set; }
        public bool _search { get; set; }
        public Int32 VersionId { get; set; }
        public int NodeTypeId { get; set; }
        public string NewNodeTypeName { get; set; }
        public Boolean isOwnParent { get; set; }
        public string imageName { get; set; }


        public string GetVersions()
        {
            try
            {

                Util.WritInfoLog(ClsName, "GetVersions", "Start Creating Connection");
                Connection sqlConn = new Connection();
                Util.WritInfoLog(ClsName, "GetVersions", "End Creating Connection");

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@HierarchyId", DbType.Int32, ParameterDirection.Input, 0, HierarchyId));

                Util.WritInfoLog(ClsName, "GetVersions", "method of usp_GetVersions", string.Format("method:{0}", "usp_GetVersions"));
                DataSet ds = sqlConn.GetDataSetFromFactoryProc("usp_GetVersions", ref(lstparam));
                Util.WritInfoLog(ClsName, "GetVersions", "End method of usp_GetVersions");

                List<DropdownListItem> listVersions = new List<DropdownListItem>();
                listVersions.Add(new DropdownListItem("--Select--", ""));
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    listVersions.Add(new DropdownListItem(dr["VERSION_NAME"].ToString(), dr["VERSION_ID"].ToString(), Convert.ToBoolean(dr["IS_PUBLISHED"])));
                }

                JavaScriptSerializer js = new JavaScriptSerializer();
                return js.Serialize(listVersions);

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "GetVersions", "Error in GetVersions", "", ex);
                throw ex;
            }
        }

        public string GetHierColumns()
        {
            try
            {

                string primarykey = string.Empty;

                Connection sqlConn = new Connection();

                Util.WritInfoLog(ClsName, "GetHierColumns", "procedure for GetHierColumns", "usp_GetHierColumns");
                DataSet ds = sqlConn.GetDataSetFromFactoryProc("usp_GetHierColumns");
                Util.WritInfoLog(ClsName, "GetHierColumns", "End procedure for GetHierColumns");

                Common common = new Common();

                Grid griddata = new Grid();

                DataTable dt = ds.Tables[0];
                DataRow[] rows = dt.Select("DISPLAY = 'NODE_TABLE_NAME' OR DISPLAY = 'HIERARCHY_ID'");

                // If you don't want to select a specific id 
                // ignore the parameter for select. 
                for (int i = 0; i < rows.Length; i++)
                { rows[i]["IS_HIDDEN"] = 1; }

                //set columns,colmodel,primary key
                // griddata = common.SetColModel(griddata, ds.Tables[0]);
                griddata = common.SetColModel(griddata, dt);

                JavaScriptSerializer js = new JavaScriptSerializer();
                return js.Serialize(griddata);

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "GetHierColumns", "Error in GetHierColumns", "", ex);
                throw ex;
            }
        }

        public string GetHierarchydata()
        {
            try
            {

                Connection sqlConn = new Connection();
                if (sidx == "" || sidx == null)
                {
                    sidx = " HIERARCHY_ID ";
                }

                string sortExp = sidx + " " + sord;
                int start = ((page - 1) * rows);

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@start", DbType.Int32, ParameterDirection.Input, 0, start));
                lstparam.Add(new DBParameter("@rows", DbType.Int32, ParameterDirection.Input, 0, rows));
                lstparam.Add(new DBParameter("@query", DbType.String, ParameterDirection.Input, 1000, query.Replace("\"", "'")));
                lstparam.Add(new DBParameter("@sortExp", DbType.String, ParameterDirection.Input, 1000, sortExp));
                lstparam.Add(new DBParameter("@_search", DbType.Boolean, ParameterDirection.Input, 0, _search));

                Util.WritInfoLog(ClsName, "GetHierarchydata", "method for GetHierarchydata");
                DataSet ds = sqlConn.GetDataSetFromFactoryProc("usp_GetHierarchydata", ref(lstparam));
                Util.WritInfoLog(ClsName, "GetHierarchydata", "End method for GetHierarchydata");
                ///////

                DataTable dt = ds.Tables[0];

                int cnt = 0;
                cnt = Convert.ToInt32(ds.Tables[1].Rows[0]["CNT"]);
                string strData = Common.JsonForJqgrid(ds.Tables[0], rows, cnt, page);

                return strData;
            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "GetHierarchydata", "Error in GetHierarchydata", "", ex);
                throw ex;
            }
        }

        public string GetVerColumns()
        {
            try
            {

                string primarykey = string.Empty;
                Connection sqlConn = new Connection();

                Util.WritInfoLog(ClsName, "GetVerColumns", "procedure for GetVerColumns", "usp_GetVerColumns");
                DataSet ds = sqlConn.GetDataSetFromFactoryProc("usp_GetVerColumns");
                Util.WritInfoLog(ClsName, "GetVerColumns", "End procedure for GetVerColumns");

                Common common = new Common();
                Grid griddata = new Grid();

                DataTable dt = ds.Tables[0];
                DataRow[] rows = dt.Select("DISPLAY = 'HIERARCHY_ID' OR DISPLAY = 'VERSION_ID' OR DISPLAY = 'ID' OR DISPLAY ='VERSION_STATUS'");

                // If you don't want to select a specific id 
                // ignore the parameter for select. 
                for (int i = 0; i < rows.Length; i++)
                { rows[i]["IS_HIDDEN"] = 1; }

                DataRow[] rowsD = dt.Select("DISPLAY = 'START_DATE' OR DISPLAY = 'END_DATE'");
                for (int i = 0; i < rowsD.Length; i++)
                { rowsD[i]["PRECISION"] = 10; }


                ds.Tables[0].TableName = "X_HIER_VERSIONS";
                //set columns,colmodel,primary key
                griddata = common.SetColModel(griddata, ds.Tables[0]);

                JavaScriptSerializer js = new JavaScriptSerializer();
                return js.Serialize(griddata);

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "GetVerColumns", "Error in GetVerColumns", "", ex);
                throw ex;
            }
        }

        public string GetVersiondata()
        {
            try
            {

                Connection sqlConn = new Connection();
                if (sidx == "" || sidx == null)
                {
                    sidx = " VERSION_ID ";
                }

                string sortExp = sidx + " " + sord;
                int start = ((page - 1) * rows);

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@start", DbType.Int32, ParameterDirection.Input, 0, start));
                lstparam.Add(new DBParameter("@rows", DbType.Int32, ParameterDirection.Input, 0, rows));
                lstparam.Add(new DBParameter("@query", DbType.String, ParameterDirection.Input, 1000, query.Replace("\"", "'")));
                lstparam.Add(new DBParameter("@sortExp", DbType.String, ParameterDirection.Input, 1000, sortExp));
                lstparam.Add(new DBParameter("@_search", DbType.Boolean, ParameterDirection.Input, 0, _search));
                lstparam.Add(new DBParameter("@HierarchyId", DbType.Int32, ParameterDirection.Input, 0, HierarchyId));

                Util.WritInfoLog(ClsName, "GetVersiondata", "method for GetVersiondata");
                DataSet ds = sqlConn.GetDataSetFromFactoryProc("usp_GetVersiondata", ref(lstparam));
                Util.WritInfoLog(ClsName, "GetVersiondata", "End method for GetVersiondata");

                DataTable dt = ds.Tables[0];

                int cnt = 0;
                cnt = Convert.ToInt32(ds.Tables[1].Rows[0]["CNT"]);
                string strData = Common.JsonForJqgrid(ds.Tables[0], rows, cnt, page);

                return strData;

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "GetVersiondata", "Error in GetVersiondata", "", ex);
                throw ex;
            }
        }

        public string GetChildren()
        {
            try
            {

                Util.WritInfoLog(ClsName, "GetChildren", "Start Creating Connection");
                Connection sqlConn = new Connection();
                Util.WritInfoLog(ClsName, "GetChildren", "End Creating Connection");

                List<DBParameter> lstparam = new List<DBParameter>();
                //lstparam.Add(new DBParameter("@txtDate", DbType.DateTime, ParameterDirection.Input, 0, txtDate));
                lstparam.Add(new DBParameter("@IsRoot", DbType.Boolean, ParameterDirection.Input, 0, IsRoot));
                lstparam.Add(new DBParameter("@ParentId", DbType.Int32, ParameterDirection.Input, 0, ParentId));
                lstparam.Add(new DBParameter("@HierarchyId", DbType.Int32, ParameterDirection.Input, 0, HierarchyId));
                lstparam.Add(new DBParameter("@VersionId", DbType.Int32, ParameterDirection.Input, 0, VersionId));

                Util.WritInfoLog(ClsName, "GetChildren", "method of GetChildren", string.Format("method:{0}", "usp_GetNodeChildren"));
                DataSet ds = sqlConn.GetDataSetFromFactoryProc("usp_GetNodeChildren", ref(lstparam));
                Util.WritInfoLog(ClsName, "GetChildren", "End method of GetChildren");

                List<Node> listNodes = new List<Node>();

                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    NodeProperty nodeProperty = new NodeProperty(dr["NODE_TYPE_ID"].ToString(), dr["NODE_TYPE_NAME"].ToString(), dr["NODE_TYPE_ID"].ToString(), dr["IS_LEAF"].ToString(), dr["ValidParent"].ToString(), Convert.ToBoolean(dr["IsOwnParent"]));
                    // Node node;
                    //if (Convert.ToBoolean(dr["IsOwnParent"]) == true)
                    //{
                    //    node = new Node(dr["NODE_TYPE_NAME"].ToString() + "<strong style='color:red' >*</strong>", "closed", nodeProperty);
                    //}
                    //else
                    //{
                    Node node = new Node(dr["NODE_TYPE_NAME"].ToString(), "closed", nodeProperty);
                    //}

                    listNodes.Add(node);
                }

                JavaScriptSerializer js = new JavaScriptSerializer();
                return js.Serialize(listNodes);

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "GetChildren", "Error in GetChildren", "", ex);
                throw ex;
            }
        }

        public Int32 CreateNodeType()
        {
            try
            {

                Util.WritInfoLog(ClsName, "CreateNodeType", "Start Creating Connection");
                Connection sqlConn = new Connection();
                Util.WritInfoLog(ClsName, "CreateNodeType", "End Creating Connection");

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@HierarchyId", DbType.Int32, ParameterDirection.Input, 0, HierarchyId));
                lstparam.Add(new DBParameter("@VersionId", DbType.Int32, ParameterDirection.Input, 0, VersionId));
                lstparam.Add(new DBParameter("@ParentId", DbType.Int32, ParameterDirection.Input, 0, ParentId));//@ParentId
                lstparam.Add(new DBParameter("@NewNodeName", DbType.String, ParameterDirection.Input, 255, NewNodeTypeName));
                lstparam.Add(new DBParameter("@isOwnParent", DbType.Boolean, ParameterDirection.Input, 255, isOwnParent));

                if (imageName != null || imageName != "")
                {
                    lstparam.Add(new DBParameter("@imageName", DbType.String, ParameterDirection.Input, 255, imageName));
                }

                lstparam.Add(new DBParameter("@NEW_TYPE_ID", DbType.Int32, ParameterDirection.Output, 0, 0));
                //lstparam.Add(new DBParameter("@COUNT", DbType.Int32, ParameterDirection.Output, 0, 0));

                Util.WritInfoLog(ClsName, "CreateNodeType", "method for create node", "usp_CreateNodeType");
                Int32 res = sqlConn.SaveDataByFactoryByProc("usp_CreateNodeType", ref(lstparam));
                Util.WritInfoLog(ClsName, "CreateNodeType", "End method for create node");

                Int32 ident = 0;
                //int retVal = 0;

                var iden = from param in lstparam
                           where param.name == "@NEW_TYPE_ID"
                           select param.value;

                ident = Convert.ToInt32((iden.First() is DBNull) ? 0 : iden.First());

                //var retVal1 = from param in lstparam
                //              where param.name == "@COUNT"
                //              select param.value;

                //retVal = Convert.ToInt32((retVal1.First() is DBNull) ? 0 : retVal1.First());
                //return ident + ":" + retVal;

                return ident;
                //return "12";

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "CreateNodeType", "CreateNodeType", "", ex);
                throw ex;
            }
        }

        public bool ValidateNodeTypeName()
        {
            try
            {

                Util.WritInfoLog(ClsName, "ValidateNodeTypeName", "Start Creating Connection");
                Connection sqlConn = new Connection();
                Util.WritInfoLog(ClsName, "ValidateNodeTypeName", "End Creating Connection");

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@HierarchyId", DbType.Int32, ParameterDirection.Input, 0, HierarchyId));
                lstparam.Add(new DBParameter("@VersionId", DbType.Int32, ParameterDirection.Input, 0, VersionId));
                lstparam.Add(new DBParameter("@NewNodeName", DbType.String, ParameterDirection.Input, 255, NewNodeTypeName));
                lstparam.Add(new DBParameter("@isDuplicate", DbType.Boolean, ParameterDirection.Output, 0, false));

                Util.WritInfoLog(ClsName, "ValidateNodeTypeName", "method for validate node type name", "usp_ValidateNodeTypeName");
                Int32 res = sqlConn.SaveDataByFactoryByProc("usp_ValidateNodeTypeName", ref(lstparam));
                Util.WritInfoLog(ClsName, "ValidateNodeTypeName", "End method for validate node type name");

                Boolean isDuplicate = false;

                var isDup = from param in lstparam
                            where param.name == "@isDuplicate"
                            select param.value;

                isDuplicate = Convert.ToBoolean((isDup.First() is DBNull) ? false : isDup.First());
                return isDuplicate;

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "ValidateNodeTypeName", "ValidateNodeTypeName", "", ex);
                throw ex;
            }
        }

        public string GetAttrColumns()
        {
            try
            {

                string primarykey = string.Empty;
                Connection sqlConn = new Connection();

                Util.WritInfoLog(ClsName, "GetAttrColumns", "procedure for GetAttrColumns", "usp_GetAttrColumns");
                DataSet ds = sqlConn.GetDataSetFromFactoryProc("usp_GetAttrColumns");
                Util.WritInfoLog(ClsName, "GetAttrColumns", "End procedure for GetAttrColumns");

                Common common = new Common();

                Grid griddata = new Grid();

                DataTable dt = ds.Tables[0];
                DataRow[] rows = dt.Select("DISPLAY = 'HIERARCHY_ID' OR DISPLAY = 'VERSION_ID' OR DISPLAY = 'NODE_TYPE_ID' OR DISPLAY = 'IS_REQUIRED' OR DISPLAY = 'ATTR_ID'");//IS_REQUIRED

                // If you don't want to select a specific id 
                // ignore the parameter for select. 
                for (int i = 0; i < rows.Length; i++)
                { rows[i]["IS_HIDDEN"] = 1; }

                //DataRow dr = dt.AsEnumerable().Where(r => ((string)r["FIELD_NAME"]).Equals("ATTR_PRECISION")).First(); 
                //dr["IS_REQUIRED"] =true; 

                //set columns,colmodel,primary key
                griddata = common.SetColModel(griddata, dt);

                JavaScriptSerializer js = new JavaScriptSerializer();
                return js.Serialize(griddata);

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "GetAttrColumns", "Error in GetAttrColumns", "", ex);
                throw ex;
            }
        }

        public string GetAttrdata()
        {
            try
            {

                Connection sqlConn = new Connection();
                if (sidx == "" || sidx == null)
                {
                    sidx = " VERSION_ID ";
                }

                string sortExp = sidx + " " + sord;
                int start = ((page - 1) * rows);

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@start", DbType.Int32, ParameterDirection.Input, 0, start));
                lstparam.Add(new DBParameter("@rows", DbType.Int32, ParameterDirection.Input, 0, rows));
                lstparam.Add(new DBParameter("@query", DbType.String, ParameterDirection.Input, 1000, query.Replace("\"", "'")));
                lstparam.Add(new DBParameter("@sortExp", DbType.String, ParameterDirection.Input, 1000, sortExp));
                lstparam.Add(new DBParameter("@_search", DbType.Boolean, ParameterDirection.Input, 0, _search));
                lstparam.Add(new DBParameter("@HierarchyId", DbType.Int32, ParameterDirection.Input, 0, HierarchyId));
                lstparam.Add(new DBParameter("@VersionId", DbType.Int32, ParameterDirection.Input, 0, VersionId));
                lstparam.Add(new DBParameter("@NodeTypeId", DbType.Int32, ParameterDirection.Input, 0, NodeTypeId));

                Util.WritInfoLog(ClsName, "GetAttrdata", "method for GetAttrdata");
                DataSet ds = sqlConn.GetDataSetFromFactoryProc("usp_GetAttrdata", ref(lstparam));
                Util.WritInfoLog(ClsName, "GetAttrdata", "End method for GetAttrdata");

                DataTable dt = ds.Tables[0];
                dt.Columns.Remove("Row");
                //dt.Columns.Remove("IS_REQUIRED");

                int cnt = 0;
                cnt = Convert.ToInt32(ds.Tables[1].Rows[0]["CNT"]);
                string strData = Common.JsonForJqgrid(dt, rows, cnt, page);

                return strData;

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "GetAttrdata", "Error in GetAttrdata", "", ex);
                throw ex;
            }
        }

        public string AddNodeAttr()
        {
            try
            {

                Connection sqlConn = new Connection();

                List<dict> allkey = new List<dict>();

                foreach (string key in allkeys)
                {
                    dict d = new dict();
                    d.key1 = key;
                    d.value = allkeys[key];
                    allkey.Add(d);
                }

                var xmlDocument = new XmlDocument();
                var serializer = new XmlSerializer(typeof(List<dict>));

                using (var stream = new MemoryStream())
                {
                    serializer.Serialize(stream, allkey);
                    stream.Flush(); stream.Seek(0, SeekOrigin.Begin);
                    xmlDocument.Load(stream);
                }

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@field_Xml", DbType.Xml, ParameterDirection.Input, 0, xmlDocument.OuterXml));
                lstparam.Add(new DBParameter("@NodeTypeId", DbType.Int32, ParameterDirection.Input, 0, NodeTypeId));
                lstparam.Add(new DBParameter("@returnValue", DbType.Int16, ParameterDirection.ReturnValue, 0));
                lstparam.Add(new DBParameter("@HierarchyId", DbType.Int32, ParameterDirection.Input, 0, HierarchyId));
                lstparam.Add(new DBParameter("@VersionId", DbType.Int32, ParameterDirection.Input, 0, VersionId));

                Util.WritInfoLog(ClsName, "AddNodeAttr", "method for AddNodeAttr");
                Int32 res = Convert.ToInt32(sqlConn.SaveDataByFactoryByProc("usp_AddNodeAttr", ref(lstparam)));
                Util.WritInfoLog(ClsName, "AddNodeAttr", "End method for AddNodeAttr");

                int retVal = 0;
                foreach (DBParameter dbparam in lstparam)
                {
                    if (dbparam.name == "@returnValue")
                    {
                        retVal = Convert.ToInt32(dbparam.value);
                        break;
                    }
                }

                if (retVal == 0) return "false";
                else return Convert.ToString(res);

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "AddNodeAttr", "Error in AddNodeAttr", "", ex);
                throw ex;
            }
        }

        public string UpdateNodeTypeName()
        {
            try
            {

                Util.WritInfoLog(ClsName, "UpdateNodeTypeName", "Start Creating Connection");
                Connection sqlConn = new Connection();
                Util.WritInfoLog(ClsName, "UpdateNodeTypeName", "End Creating Connection");

                //string strQry = " UPDATE " + TableName + " SET NODE_NAME = '" + NewNodeName + "' WHERE NODE_ID = " + NodeId.ToString();
                JavaScriptSerializer js = new JavaScriptSerializer();

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@NewNodeTypeName", DbType.String, ParameterDirection.Input, 0, NewNodeTypeName));
                lstparam.Add(new DBParameter("@NodeTypeId", DbType.Int32, ParameterDirection.Input, 0, NodeTypeId));
                lstparam.Add(new DBParameter("@HierarchyId", DbType.Int32, ParameterDirection.Input, 0, HierarchyId));
                lstparam.Add(new DBParameter("@VersionId", DbType.Int32, ParameterDirection.Input, 0, VersionId));

                Util.WritInfoLog(ClsName, "UpdateNodeTypeName", "method of usp_UpdateNodeTypeName", string.Format("method:{0}", "usp_UpdateNodeTypeName"));
                Int32 ret = Convert.ToInt32(sqlConn.SaveDataByFactoryByProc("usp_UpdateNodeTypeName", ref(lstparam)));
                Util.WritInfoLog(ClsName, "UpdateNodeTypeName", "End method of usp_UpdateNodeTypeName");

                return js.Serialize(new Records(ret));

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "UpdateNodeTypeName", "UpdateNodeTypeName", "", ex);
                throw ex;
            }
        }

        public string RemoveNodeType()
        {

            try
            {

                Util.WritInfoLog(ClsName, "RemoveNodeType", "Start Creating Connection");
                Connection sqlConn = new Connection();
                Util.WritInfoLog(ClsName, "RemoveNodeType", "End Creating Connection");

                //update end_date to node which we have to remove
                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@NodeTypeId", DbType.Int32, ParameterDirection.Input, 0, NodeTypeId));
                lstparam.Add(new DBParameter("@HierarchyId", DbType.Int32, ParameterDirection.Input, 0, HierarchyId));
                lstparam.Add(new DBParameter("@VersionId", DbType.Int32, ParameterDirection.Input, 0, VersionId));

                Util.WritInfoLog(ClsName, "RemoveNodeType", "method of usp_RemoveNodeType", string.Format("method:{0}", "usp_RemoveNodeType"));
                Int32 ret = Convert.ToInt32(sqlConn.SaveDataByFactoryByProc("usp_RemoveNodeType", ref(lstparam)));
                Util.WritInfoLog(ClsName, "RemoveNodeType", "End method of usp_RemoveNodeType");

                return "true";

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "RemoveNodeType", "RemoveNodeType", "", ex);
                return "false";
                throw ex;
            }
        }

        public string delX_HIERARCHIES()
        {
            try
            {

                Connection sqlConn = new Connection();

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@HierarchyId", DbType.Int32, ParameterDirection.Input, 0, HierarchyId));
                lstparam.Add(new DBParameter("@returnValue", DbType.Int32, ParameterDirection.ReturnValue));

                Util.WritInfoLog(ClsName, "delX_HIERARCHIES", "method for delX_HIERARCHIES", "usp_delX_HIERARCHIES");
                Int32 res = sqlConn.SaveDataByFactoryByProc("usp_delX_HIERARCHIES", ref(lstparam));
                Util.WritInfoLog(ClsName, "delX_HIERARCHIES", "End method for delX_HIERARCHIES");

                int retVal = 0;

                var retValue = from param in lstparam
                               where param.name == "@returnValue"
                               select param.value;

                retVal = Convert.ToInt16((retValue.First() is DBNull) ? 0 : retValue.First());

                if (retVal == 0) return "false"; else return "true";

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "delX_HIERARCHIES", "Error in delX_HIERARCHIES", "", ex);
                throw ex;
            }
        }

        public string EditHierarchy()
        {
            try
            {

                Connection sqlConn = new Connection();

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@HIER_NAME", DbType.String, ParameterDirection.Input, 255, allkeys["HIER_NAME"]));
                lstparam.Add(new DBParameter("@HIER_ABBR_CODE", DbType.String, ParameterDirection.Input, 255, allkeys["HIER_ABBR_CODE"].ToUpper()));
                lstparam.Add(new DBParameter("@HierarchyId", DbType.Int32, ParameterDirection.Input, 255, HierarchyId));
                lstparam.Add(new DBParameter("@returnValue", DbType.Int16, ParameterDirection.ReturnValue));

                Util.WritInfoLog(ClsName, "EditHierarchy", "method for Edit Hierarchy", "usp_EditHierarchy");
                int ret = sqlConn.SaveDataByFactoryByProc("usp_EditHierarchy", ref(lstparam));
                Util.WritInfoLog(ClsName, "EditHierarchy", "End method for Edit Hierarchy");

                Int16 retVal = 0;

                var retValue = from param in lstparam
                               where param.name == "@returnValue"
                               select param.value;

                retVal = Convert.ToInt16((retValue.First() is DBNull) ? 0 : retValue.First());

                if (retVal == -1)
                {
                    return "false| Hierarchy Name And Abbr should be unique !";
                }
                else
                {
                    if (retVal == 0) return "false";
                    else return "true";
                }
            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "EditHierarchy", "Error in EditHierarchy", "", ex);
                return "false";
                throw ex;
            }
        }

        public string AddHierarchy()
        {
            try
            {

                Connection sqlConn = new Connection();

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@HIER_NAME", DbType.String, ParameterDirection.Input, 255, allkeys["HIER_NAME"]));
                lstparam.Add(new DBParameter("@HIER_ABBR_CODE", DbType.String, ParameterDirection.Input, 255, allkeys["HIER_ABBR_CODE"].ToUpper()));
                lstparam.Add(new DBParameter("@returnValue", DbType.Int16, ParameterDirection.ReturnValue));

                Util.WritInfoLog(ClsName, "AddHierarchy", "method for Add Hierarchy", "usp_AddHierarchy");
                int ret = sqlConn.SaveDataByFactoryByProc("usp_AddHierarchy", ref(lstparam));
                Util.WritInfoLog(ClsName, "AddHierarchy", "End method for Add Hierarchy");

                Int16 retVal = 0;

                var retValue = from param in lstparam
                               where param.name == "@returnValue"
                               select param.value;

                retVal = Convert.ToInt16((retValue.First() is DBNull) ? 0 : retValue.First());

                if (retVal == -1)
                {
                    return "false| Hierarchy Name And Abbr should be unique !";
                }
                else
                {
                    if (retVal == 0) return "false";
                    else return retVal.ToString();
                }
            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "AddHierarchy", "Error in AddHierarchy", "", ex);
                return "false";
                throw ex;
            }
        }

        public string delVersion()
        {
            try
            {

                Connection sqlConn = new Connection();

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@ID", DbType.Int32, ParameterDirection.Input, 0, VersionId));
                lstparam.Add(new DBParameter("@returnValue", DbType.Int32, ParameterDirection.ReturnValue));

                Util.WritInfoLog(ClsName, "delVersion", "method for delVersion", "usp_delVersion");
                Int32 res = sqlConn.SaveDataByFactoryByProc("usp_delVersion", ref(lstparam));
                Util.WritInfoLog(ClsName, "delVersion", "End method for delVersion");

                int retVal = 0;

                var retValue = from param in lstparam
                               where param.name == "@returnValue"
                               select param.value;

                retVal = Convert.ToInt16((retValue.First() is DBNull) ? 0 : retValue.First());

                if (retVal == -1)
                {
                    return "false| Node Type exists for this Version. First Delete Node Types !";
                }
                else
                {
                    if (retVal == 0) return "false";
                    else return "true";
                }

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "delVersion", "Error in delVersion", "", ex);
                throw ex;
            }
        }

        public string EditVersion()
        {
            try
            {

                Connection sqlConn = new Connection();

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@VERSION_NAME", DbType.String, ParameterDirection.Input, 255, allkeys["VERSION_NAME"]));
                lstparam.Add(new DBParameter("@START_DATE", DbType.Date, ParameterDirection.Input, 0, allkeys["START_DATE"]));
                lstparam.Add(new DBParameter("@END_DATE", DbType.Date, ParameterDirection.Input, 0, allkeys["END_DATE"]));
                lstparam.Add(new DBParameter("@VersionId", DbType.Int32, ParameterDirection.Input, 255, VersionId));
                lstparam.Add(new DBParameter("@HierarchyId", DbType.Int32, ParameterDirection.Input, 255, HierarchyId));
                lstparam.Add(new DBParameter("@returnValue", DbType.Int16, ParameterDirection.ReturnValue));

                Util.WritInfoLog(ClsName, "EditVersion", "method for Edit Hierarchy", "usp_EditVersion");
                int ret = sqlConn.SaveDataByFactoryByProc("usp_EditVersion", ref(lstparam));
                Util.WritInfoLog(ClsName, "EditVersion", "End method for Edit Hierarchy");

                Int16 retVal = 0;

                var retValue = from param in lstparam
                               where param.name == "@returnValue"
                               select param.value;

                retVal = Convert.ToInt16((retValue.First() is DBNull) ? 0 : retValue.First());

                if (retVal == -1)
                {
                    return "false| Version Name should be unique !";
                }
                else
                {
                    if (retVal == 0) return "false";
                    else return "true";
                }
            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "EditVersion", "Error in EditVersion", "", ex);
                return "false";
                throw ex;
            }
        }

        public string AddVersion()
        {
            try
            {

                Connection sqlConn = new Connection();

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@VERSION_NAME", DbType.String, ParameterDirection.Input, 255, allkeys["VERSION_NAME"]));
                lstparam.Add(new DBParameter("@START_DATE", DbType.Date, ParameterDirection.Input, 0, allkeys["START_DATE"]));
                lstparam.Add(new DBParameter("@END_DATE", DbType.Date, ParameterDirection.Input, 0, allkeys["END_DATE"]));
                lstparam.Add(new DBParameter("@HierarchyId", DbType.Int32, ParameterDirection.Input, 255, HierarchyId));
                lstparam.Add(new DBParameter("@returnValue", DbType.Int16, ParameterDirection.ReturnValue));

                Util.WritInfoLog(ClsName, "AddVersion", "method for Add Version", "usp_AddVersion");
                int ret = sqlConn.SaveDataByFactoryByProc("usp_AddVersion", ref(lstparam));
                Util.WritInfoLog(ClsName, "AddVersion", "End method for Add Version");

                Int16 retVal = 0;

                var retValue = from param in lstparam
                               where param.name == "@returnValue"
                               select param.value;

                retVal = Convert.ToInt16((retValue.First() is DBNull) ? 0 : retValue.First());

                if (retVal == -1)
                {
                    return "false| Version Name should be unique !";
                }
                else
                {
                    if (retVal == 0) return "false";
                    else return retVal.ToString();
                }
            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "AddVersion", "Error in AddVersion", "", ex);
                return "false";
                throw ex;
            }
        }

        public string DelNodeAttr()
        {
            try
            {

                Connection sqlConn = new Connection();

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@attr_id", DbType.Int32, ParameterDirection.Input, 0, allkeys["id"]));
                lstparam.Add(new DBParameter("@returnValue", DbType.Int32, ParameterDirection.ReturnValue));

                Util.WritInfoLog(ClsName, "DelNodeAttr", "method for DelNodeAttr", "usp_DelNodeAttr");
                Int32 res = sqlConn.SaveDataByFactoryByProc("usp_DelNodeAttr", ref(lstparam));
                Util.WritInfoLog(ClsName, "DelNodeAttr", "End method for DelNodeAttr");

                int retVal = 0;

                var retValue = from param in lstparam
                               where param.name == "@returnValue"
                               select param.value;

                retVal = Convert.ToInt16((retValue.First() is DBNull) ? 0 : retValue.First());

                if (retVal == 0) return "false"; else return "true";

            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "DelNodeAttr", "Error in DelNodeAttr", "", ex);
                throw ex;
            }
        }

        public string EditNodeAttr()
        {
            try
            {

                Connection sqlConn = new Connection();

                List<dict> allkey = new List<dict>();

                foreach (string key in allkeys)
                {
                    dict d = new dict();
                    d.key1 = key;
                    d.value = allkeys[key];
                    allkey.Add(d);
                }

                var xmlDocument = new XmlDocument();
                var serializer = new XmlSerializer(typeof(List<dict>));

                using (var stream = new MemoryStream())
                {
                    serializer.Serialize(stream, allkey);
                    stream.Flush(); stream.Seek(0, SeekOrigin.Begin);
                    xmlDocument.Load(stream);
                }

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@field_Xml", DbType.Xml, ParameterDirection.Input, 0, xmlDocument.OuterXml));
                lstparam.Add(new DBParameter("@attr_id", DbType.Int32, ParameterDirection.Input, 0, Convert.ToInt32(allkeys["id"])));
                lstparam.Add(new DBParameter("@attr_name", DbType.String, ParameterDirection.Input, 255, allkeys["ATTR_NAME"]));
                lstparam.Add(new DBParameter("@returnValue", DbType.Int16, ParameterDirection.ReturnValue));
                lstparam.Add(new DBParameter("@node_type_id", DbType.Int32, ParameterDirection.Input, 0, (int)Convert.ToDouble(allkeys["NODE_TYPE_ID"])));
                lstparam.Add(new DBParameter("@VersionId", DbType.Int32, ParameterDirection.Input, 255, (int)Convert.ToDouble(allkeys["HIERARCHY_ID"])));
                lstparam.Add(new DBParameter("@HierarchyId", DbType.Int32, ParameterDirection.Input, 255, (int)Convert.ToDouble(allkeys["VERSION_ID"])));

                Util.WritInfoLog(ClsName, "EditNodeAttr", "method for Edit attribute", "usp_EditNodeAttr");
                int ret = sqlConn.SaveDataByFactoryByProc("usp_EditNodeAttr", ref(lstparam));
                Util.WritInfoLog(ClsName, "EditNodeAttr", "End method for Edit attribute");

                Int16 retVal = 0;

                var retValue = from param in lstparam
                               where param.name == "@returnValue"
                               select param.value;

                retVal = Convert.ToInt16((retValue.First() is DBNull) ? 0 : retValue.First());

                if (retVal == -1)
                {
                    return "false| Attribute Name should be unique !";
                }
                else
                {
                    if (retVal == 0) return "false";
                    else return "true";
                }
            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "EditNodeAttr", "Error in EditNodeAttr", "", ex);
                return "false";
                throw ex;
            }
        }

        public string publishHierarchy()
        {
            try
            {

                Connection sqlConn = new Connection();

                List<DBParameter> lstparam = new List<DBParameter>();
                lstparam.Add(new DBParameter("@VersionId", DbType.Int32, ParameterDirection.Input, 255, VersionId));
                lstparam.Add(new DBParameter("@HierarchyId", DbType.Int32, ParameterDirection.Input, 255, HierarchyId));
                lstparam.Add(new DBParameter("@returnValue", DbType.Int16, ParameterDirection.ReturnValue));

                Util.WritInfoLog(ClsName, "publishHierarchy", "method for Add Version", "usp_publishHierarchy");
                int ret = sqlConn.SaveDataByFactoryByProc("usp_publishHierarchy", ref(lstparam));
                Util.WritInfoLog(ClsName, "publishHierarchy", "End method for Add Version");

                Int16 retVal = 0;

                var retValue = from param in lstparam
                               where param.name == "@returnValue"
                               select param.value;

                retVal = Convert.ToInt16((retValue.First() is DBNull) ? 0 : retValue.First());

                if (retVal == -1)
                {
                    return "false| Hierarchy should have at least 1 node type except root !";
                }
                else if (retVal == -2)
                {
                    return "false| Every node type should have at least 1 attribute !";
                }
                else
                {
                    if (retVal == 0) return "false";
                    else return "true";
                }
            }
            catch (Exception ex)
            {
                Util.WritErrorLog(ClsName, "publishHierarchy", "Error in publishHierarchy", "", ex);
                return "false";
                throw ex;
            }
        }

    }
}
